package com.slc.datatransfer;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by sunguangzhu on 2015/3/17.
 */
public class XSSFUtil {
    public static String[] getHeaders(HSSFSheet hssfSheet) throws IOException {
        String[] strings=null;
        if(hssfSheet != null){
            HSSFRow hssfRow = hssfSheet.getRow(0);
             strings = new String[hssfRow.getLastCellNum()];
            for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++){
                HSSFCell hssfCell = hssfRow.getCell(cellNum);
                if(hssfCell != null){
                    strings[cellNum]= getTransformContent(hssfCell).toString();
                }
            }
        }
        return strings;
    }
    public static List<Map<String,Object>> getListMap(InputStream is) throws Exception{
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        //构造 XSSFWorkbook 对象，strPath 传入文件路径
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        //循环工作表Sheet
        for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if(hssfSheet == null){
                continue;
            }
            String[] headers = getHeaders(hssfWorkbook.getSheetAt(0));
            //循环行Row
            for(int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++){
                Map<String, Object> map = new HashMap<String, Object>();
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if(hssfRow == null){
                    continue;
                }
                //循环列Cell
                for(int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++){
                    HSSFCell hssfCell = hssfRow.getCell(cellNum);
                    if(hssfCell == null){
                        continue;
                    }
                    String s =  getTransformContent(hssfCell).toString();
                    map.put(headers[cellNum],s);
                }
                list.add(map);
            }
        }
        is.close();
        return list;
    }

    public static Object getTransformContent(HSSFCell hssfCell) {
        Object content="";
        if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){
            content=hssfCell.getBooleanCellValue();
        }else if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
            content=hssfCell.getNumericCellValue();
        }else{
            content=hssfCell.getStringCellValue();
        }
        return content;
    }

    public static void main(String[] args) {
        String excelFile = "F:\\奖品配送导出记录表.xls";
        try {
            List<Map<String, Object>> list = XSSFUtil.getListMap(new FileInputStream(excelFile));
            for (Map<String, Object> l : list) {
                for (String s : l.keySet()) {
                    System.out.print(s);
                    System.out.println(l.get(s));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }
}
